package DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import DataObjects.BusinessMenuLeftList;
import DataObjects.StandardMenuLeftList;

public class GetRecipesToLeftListForUpdBussMenu {
	
private String menuId;
	
	public GetRecipesToLeftListForUpdBussMenu(String mid)
	{
		this.menuId=mid;
	}

	public Vector<BusinessMenuLeftList> getAllRecipes()
	{
		Vector<BusinessMenuLeftList> menuRecipes=new Vector<BusinessMenuLeftList>();
		
      SQLConnection sc=new SQLConnection();
		
		
		try {
			
			PreparedStatement ps = sc.getConnection().prepareStatement("SELECT res.Rid ,res.Rname,res.Rstatus,res.Rmishkal FROM recipe res WHERE res.Rid NOT in (SELECT rbm.Rid FROM  recipe_in_menu rbm WHERE rbm.Mid=?);");
	        ps.setString(1,menuId);
			ResultSet rs=ps.executeQuery();	

			
			
			while(rs.next())
	        {   

			
				BusinessMenuLeftList leftList = new BusinessMenuLeftList();
	      
	        	 leftList.setRid(rs.getInt(1));
	        	 leftList.setName(rs.getString(2));
	        	 leftList.setStatus(rs.getString(3));
	        	 leftList.setMishkal(rs.getString(4));
	        	 	        	 
	        	 menuRecipes.add(leftList);
	         
	        }//while
			    
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//--------Calculate  price of recipe(uses last prices and quantity of items)
			try {
				
				PreparedStatement ps = sc.getConnection().prepareStatement("Select  SUM(price)" +
					                                                     	" FROM   " +
					                                                         	" (Select TRUNCATE(it.iCost*itr.iquantity,1) as price ,itr.iid,it.iCost,itr.iquantity " +
					                                                         	"     From item_in_recipe itr,item it        " +
					                                                             	"  Where itr.rid=? AND itr.iid=it.iid) AS cost");	
				
				PreparedStatement ps1 = sc.getConnection().prepareStatement("SELECT res.rconstPrice FROM recipe res Where res.rid=? ;");
				for(int i=0;i<menuRecipes.size();i++)
				{
					ps.setString(1, Integer.toString( menuRecipes.get(i).getRid()));
					ps1.setString(1, Integer.toString( menuRecipes.get(i).getRid()));
				 
				ResultSet rs=ps.executeQuery();	
				ResultSet rs1=ps1.executeQuery();	
				
				
				 while(rs.next() && rs1.next())
			        {   
	                   int numTotal;
	                   
	                   menuRecipes.get(i).setCost(Float.parseFloat (rs.getString(1)));	       //--adding  price to details of recipe  
	                   menuRecipes.get(i).setConstCost(Float.parseFloat(rs1.getString(1)));           //---adding constPrice(constant) of recipe 
					 
					 
					 
			         			        
			        }   //while
				    
					rs.close();
					rs1.close();
			        }   //for
				ps.close();
				ps1.close();
				sc.getConnection().close();
							
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
			
			
			
			return menuRecipes;
		}
		
}
